<%@page import="java.sql.*"%>
<%@page import="model.DBAdapter"%>
<%@page import="comman.Utility"%>
<%@page import="jsp.ClassJSP"%>

<%
	DBAdapter adapter = DBAdapter.getInstance();
	adapter.open();
	ResultSet rs = null;

	int id = -1;
	int classId = -1;
	int year = 0;
	String className = "";
	int enabled = -1;
	double classFee = 0;
	double dueFee = 0;
	double paidFee = 0;

	int selectedYear = -1;
	int classDetailId = -1;

	String query = "";
	String sContentHTML = "";
	boolean dataPresent = false;
	int userLevel = -1;
	try {
		userLevel = ((Integer) session.getAttribute("userLevel"))
				.intValue();
		selectedYear = ((Integer) session.getAttribute("selectedYear"))
				.intValue();

		if (request.getParameter("selectedYear") != null) {
			selectedYear = Integer.parseInt(request
					.getParameter("selectedYear"));
			session.setAttribute("selectedYear", selectedYear);
		}

	} catch (Exception e) {
		response.sendRedirect("login.jsp");
	}

	String action = request.getParameter("action");
	String searchCondition = "";
	if (action != null) {
		searchCondition = request.getParameter("searchValue");
		searchCondition = " AND class_name like '%" + searchCondition
				+ "%' ";
	}
	
	////////////////////////////////////////////////////////////////////////////
	//Code for the showing the year option tag.
	////////////////////////////////////////////////////////////////////////////
	String yearHTML = new ClassJSP().getOptionTagList(adapter, selectedYear);
	
	//////////////////////////////////////////////////////////////////////////////////////////////////////////////
	/// CODE FOR SORTING ALGORITHM START
	/////////////////////////////////////////////////////////////////////////////////////////////////////////////
	String sortBy = "class_id";
	String sortOrder = "ASC";
	String sortingCondition = " ORDER BY " + sortBy + " " + sortOrder;
	
	String imgUp = "../images/search/upDark.gif";
	String imgDown =  "../images/search/downDark.gif";
	String arrowImg = "&nbsp;&nbsp;&nbsp;<img name='tabImg' alt='' ALIGN=ABSMIDDLE src='" + imgUp+ "'/>";
	
	String sorting = request.getParameter("sorting");
	if(sorting != null) {
		sortBy = (String)request.getParameter("sortBy").toString();
		sortOrder = request.getParameter("sortOrder");
		sortingCondition = " ORDER BY " + sortBy + " " + sortOrder;
		
		if(sortOrder.equals("ASC")) {
			arrowImg = "&nbsp;&nbsp;&nbsp;<img name='tabImg' alt='' ALIGN=ABSMIDDLE src='" + imgUp+ "'/>";
		} else {
			arrowImg = "&nbsp;&nbsp;&nbsp;<img name='tabImg' alt='' ALIGN=ABSMIDDLE src='" + imgDown+ "'/>";
		}
	}
	//////////////////////////////////////////////////////////////////////////////////////////////////////////////
	/// CODE FOR SORTING ALGORITHM END HERE
	/////////////////////////////////////////////////////////////////////////////////////////////////////////////
	
	sContentHTML = sContentHTML
			+ "<table cellspacing='0' cellpadding='0' width='100%' style='border-top: 1px solid #222222'>"
			+ "<tr height = '22px' id='sorting' >"
			+ "<th id='class_id' onclick=\"return sortData('class_id');\" >&nbsp;Number"+(sortBy.intern() == "class_id"? arrowImg  : "")+"</th>"
			+ "<th id='class_name' onclick=\"return sortData('class_name');\" >Name"+(sortBy.intern() == "class_name"? arrowImg  : "")+"</th>"
			+ "<th id='class_fee' onclick=\"return sortData('class_fee');\" >Course"+(sortBy.intern() == "class_fee"? arrowImg  : "")+"</th>"
			+ "<th id='totalFeeId'>Session"+(sortBy.intern() == "totalFeeId"? arrowImg  : "")+"</th>"
			+ "<th id='dueFeeId'>Status"+(sortBy.intern() == "dueFeeId"? arrowImg  : "")+"</th>"
			+ "<th id='paidFeeId'>Date"+(sortBy.intern() == "paidFeeId"? arrowImg  : "")+"</th>"
			+ "<th style='text-align: right !important;'>Action &nbsp;</th>"
			+ "</tr>";
	
	query = "SELECT cl.id, cld.year, cl.class_id, cl.class_name, cl.description, cld.enabled, cld.class_fee, cld.class_detail_id FROM class cl, class_details cld where cl.class_id = cld.class_id  AND cld.year ="
		+ selectedYear
		+ " "
		+ searchCondition
		+ sortingCondition;
	rs = adapter.select(query);

	int classCount = 0;
	double sumClassFee = 0;
	double sumDueFee = 0;
	double sumPaidFee = 0;
	double sumTotalClassFee = 0;

	while (rs.next()) {
		id = rs.getInt("id");
		year = rs.getInt("year");
		classId = rs.getInt("class_id");
		className = rs.getString("class_name");
		enabled = rs.getInt("enabled");
		classFee = rs.getDouble("class_fee");
		classDetailId = rs.getInt("class_detail_id");

		String nameClassList = Utility.updateNameString(className);

		dueFee = 0;
		paidFee = 0;
		try {
			String queryFee = "Select total_fee, paid_fee from student_fee where class_detail_id="
					+ classDetailId + "";
			ResultSet rsFee = adapter.select(queryFee);
			while (rsFee.next()) {
				dueFee = dueFee + rsFee.getDouble("total_fee");
				paidFee = paidFee + rsFee.getDouble("paid_fee");
			}
		} catch (Exception e) {
			e.toString();
		}

		double totalFeeOfClass = dueFee + paidFee;

		sContentHTML = sContentHTML
				+ "<tr class='rowMain' height = '22px' onmouseover=\"this.oldClassName=this.className;this.className='iehover';\" onmouseout=\"this.className=this.oldClassName;\">"
				+ "<td>"
				+ "&nbsp;"
				+ classId
				+ "</td>"
				+ "<td>"
				+ className
				+ "</td>"
				+ "<td>"
				+ classFee
				+ "</td>"
				+ "<td>"
				+ (totalFeeOfClass == 0 ? "0.0" : totalFeeOfClass)
				+ "</td>"
				+ "<td>"
				+ (dueFee == 0 ? "0.0" : dueFee)
				+ "</td>"
				+ "<td>"
				+ (paidFee == 0 ? "0.0" : (paidFee))
				+ "</td>"
				+ "<td nowrap align='right'>"
				+ " [ <a style='text-decoration:none !important;' href='#' onclick=\"return showStudentList("
				+ classDetailId
				+ ");\" title='Student List'>List of Students</a>"
				+ (userLevel == 3 ? " ]&nbsp;"
						: ""
								+ " | <a style='text-decoration:none !important;' href='#' onclick=\"return editClass("
								+ id
								+ ");\" title='Edit Class.'>Edit</a>"
								+ (userLevel == 2 ? " ]&nbsp;"
										: ""
												+ " | <a style='text-decoration:none !important;' href='#' onclick=\"return deleteClass('"
												+ classDetailId
												+ "','"
												+ nameClassList
												+ "');\" title='Delete this Class.'>X</a> ]&nbsp;"))
				+ "</td>" + "</tr>";

		dataPresent = true;

		classCount++;
		sumClassFee += classFee;
		sumTotalClassFee += totalFeeOfClass;
		sumDueFee += dueFee;
		sumPaidFee += paidFee;

	}

	if (!dataPresent) {
		sContentHTML = "<table cellspacing='0' cellpadding='0' width='100%'>"
				+ "<tr class='rowSearch' height = '22px' onmouseover=\"this.oldClassName=this.className;this.className='iehover';\" onmouseout=\"this.className=this.oldClassName;\">"
				+ "<td>" + "&nbsp;";
		if (action != null) {
			sContentHTML = sContentHTML
					+ "No Class found for this search criteria.";
		} else {
			sContentHTML = sContentHTML
					+ "No Class have been configured.";
		}

		sContentHTML = sContentHTML + "</td>" + "</tr>" + "</table>";

	} else {
		sContentHTML = sContentHTML
				+ "<tr height = '22px'>"
				+ "<th>&nbsp;Total: </th>"
				+ "<th>"
				+ classCount
				+ "</th>"
				+ "<th>"
				+ sumClassFee
				+ "</th>"
				+ "<th>"
				+ sumTotalClassFee
				+ "</th>"
				+ "<th>"
				+ sumDueFee
				+ "</th>"
				+ "<th>"
				+ sumPaidFee
				+ "</th>"
				+ "<th style='text-align: right !important;'>&nbsp;</th>";

		sContentHTML = sContentHTML + "</table>";
	}

	adapter.close();
%>

<table id="content" style="cellpadding:0px; width:100%; border:0px">
	<tr >
		<td width="10px;">
			<div class="legendVirtual" >Class</div>
		</td>
		<td style="width:940px;" align="center">
			<select id="year" name="year" size="1" style="width: 205px;"  onchange="yearSelection(this.value);" >
				<%=yearHTML%>
		    </select>
		</td>
		<td width="20px;" align="right">
			<input type="button" name = "add"  id="add" value="Add" class="buttonSave"  onmouseover='return colorChangeTemporary("add");' onmouseout='return colorChangeUnDo("add");' onclick="return loadContent('addEditClass');">				
		</td>
	</tr>
	
	<tr>
		<td colspan="3">
			<%=sContentHTML%>
		</td>
		
	</tr>
</table>	
</form>	